const { query } = require('../config/db');

class CooperationApplication {
  /**
   * 创建合作申请
   * @param {Object} applicationData - 申请数据
   * @returns {Promise<Object>} 创建的申请信息
   */
  static async create(applicationData) {
    try {
      const { name, phone, intention, description } = applicationData;
      
      const result = await query(`
        INSERT INTO cooperation_applications (
          name, phone, intention, description, status, created_at
        ) VALUES (?, ?, ?, ?, 'pending', NOW())
      `, [name, phone, intention, description]);

      return await this.findById(result.insertId);
    } catch (error) {
      console.error('创建合作申请失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID查找申请
   * @param {number} id - 申请ID
   * @returns {Promise<Object|null>} 申请信息
   */
  static async findById(id) {
    try {
      const applications = await query(
        'SELECT * FROM cooperation_applications WHERE id = ?',
        [id]
      );

      return applications.length > 0 ? applications[0] : null;
    } catch (error) {
      console.error('查找合作申请失败:', error);
      throw error;
    }
  }

  /**
   * 获取申请列表（分页）
   * @param {Object} options - 查询选项
   * @returns {Promise<Object>} 申请列表和总数
   */
  static async getList(options = {}) {
    try {
      const {
        page = 1,
        pageSize = 10,
        search = '',
        status = null,
        intention = null,
        start_date = null,
        end_date = null,
        sort = 'created_at',
        order = 'DESC'
      } = options;

      const offset = (page - 1) * pageSize;
      let whereClause = 'WHERE 1=1';
      let params = [];

      if (search) {
        whereClause += ' AND (name LIKE ? OR phone LIKE ?)';
        params.push(`%${search}%`, `%${search}%`);
      }

      if (status) {
        whereClause += ' AND status = ?';
        params.push(status);
      }

      if (intention) {
        whereClause += ' AND intention = ?';
        params.push(intention);
      }

      if (start_date) {
        whereClause += ' AND DATE(created_at) >= ?';
        params.push(start_date);
      }

      if (end_date) {
        whereClause += ' AND DATE(created_at) <= ?';
        params.push(end_date);
      }

      // 获取总数
      const countResult = await query(`
        SELECT COUNT(*) as total 
        FROM cooperation_applications 
        ${whereClause}
      `, params);
      const total = countResult[0].total;

      // 获取列表
      const applications = await query(`
        SELECT *
        FROM cooperation_applications
        ${whereClause}
        ORDER BY ${sort} ${order}
        LIMIT ${parseInt(pageSize)} OFFSET ${parseInt(offset)}
      `, params);

      return { applications, total };
    } catch (error) {
      console.error('获取合作申请列表失败:', error);
      throw error;
    }
  }

  /**
   * 更新申请状态
   * @param {number} id - 申请ID
   * @param {string} status - 新状态
   * @param {string} remark - 备注
   * @returns {Promise<Object>} 更新后的申请信息
   */
  static async updateStatus(id, status, remark = '') {
    try {
      await query(
        'UPDATE cooperation_applications SET status = ?, remark = ?, updated_at = NOW() WHERE id = ?',
        [status, remark, id]
      );

      return await this.findById(id);
    } catch (error) {
      console.error('更新合作申请状态失败:', error);
      throw error;
    }
  }

  /**
   * 获取申请统计
   * @returns {Promise<Object>} 统计数据
   */
  static async getStats() {
    try {
      // 总申请数
      const totalResult = await query('SELECT COUNT(*) as total FROM cooperation_applications');
      const total = totalResult[0].total;

      // 今日申请数
      const todayResult = await query(
        'SELECT COUNT(*) as today FROM cooperation_applications WHERE DATE(created_at) = CURDATE()'
      );
      const today = todayResult[0].today;

      // 待处理申请数
      const pendingResult = await query(
        'SELECT COUNT(*) as pending FROM cooperation_applications WHERE status = "pending"'
      );
      const pending = pendingResult[0].pending;

      // 各状态统计
      const statusResult = await query(`
        SELECT 
          status,
          COUNT(*) as count
        FROM cooperation_applications 
        GROUP BY status
      `);

      const statusStats = {};
      statusResult.forEach(item => {
        statusStats[item.status] = item.count;
      });

      // 各合作意向统计
      const intentionResult = await query(`
        SELECT 
          intention,
          COUNT(*) as count
        FROM cooperation_applications 
        GROUP BY intention
      `);

      const intentionStats = {};
      intentionResult.forEach(item => {
        intentionStats[item.intention] = item.count;
      });

      return {
        total,
        today,
        pending,
        statusStats,
        intentionStats
      };
    } catch (error) {
      console.error('获取合作申请统计失败:', error);
      throw error;
    }
  }

  /**
   * 删除申请
   * @param {number} id - 申请ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async delete(id) {
    try {
      const result = await query('DELETE FROM cooperation_applications WHERE id = ?', [id]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除合作申请失败:', error);
      throw error;
    }
  }
}

module.exports = CooperationApplication;